package com.crm.controller.enqController;

import java.io.File;
import java.io.FileOutputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.exception.ConstraintViolationException;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.servlet.ModelAndView;

import com.crm.dao.AttachmentHistoryDAO;
import com.crm.dao.ClientdetDAO;
import com.crm.dao.ExecutivesDAO;
import com.crm.dao.OpportunityDAO;
import com.crm.dao.PartsDAO;
import com.crm.dao.StockDAO;
import com.crm.entity.AttachmentHistory;
import com.crm.entity.Business;
import com.crm.entity.ClientUser;
import com.crm.entity.Clientdet;
import com.crm.entity.Executives;
import com.crm.entity.Opportunity;
import com.crm.entity.Parts;
import com.crm.entity.StockEntity;
import com.crm.entity.User;
import com.crm.util.Constants;
import com.crm.util.SendMail;
import com.crm.util.xlsRead.ReadExcel;

@Controller
@RequestMapping("/newEnq")
public class NewEnqController {
	protected final Log logger = LogFactory.getLog(getClass());
	//OpportunityDAO opportunityDAO=new OpportunityDAO();
	ClientdetDAO clientdetDAO=new ClientdetDAO();
	ExecutivesDAO executivesDAO=new ExecutivesDAO();
	AttachmentHistoryDAO attachmentDAO = new AttachmentHistoryDAO();
	Opportunity opportunity=new Opportunity();
	PartsDAO partsDAO=new PartsDAO();
	StockDAO stockDAO=new StockDAO();
	
	List<Clientdet> clientsList=new ArrayList<Clientdet>();
	List<Executives> executivesList=new ArrayList<Executives>();
	List<Parts> partsList=new ArrayList<Parts>();
	//this method is called when clicked on new enquiry
	@RequestMapping("/open.do")
	public ModelAndView open(HttpServletRequest req, HttpSession session,ModelAndView model) throws Exception{
		Business bus=(Business)session.getAttribute("business");
		if(bus==null)return new ModelAndView("/jsp/index.jsp");
		clientsList=new ArrayList<Clientdet>();
		executivesList=new ArrayList<Executives>();
		int busId=bus.getBusinessId();
		
		
		try {
			clientsList=clientdetDAO.getorg(busId);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		executivesList=executivesDAO.findAll(busId);
		model.setViewName("/jsp/New Enquiry.jsp");
		model.addObject("orgList",clientsList);
		model.addObject("executives",executivesList);
		model.addObject("submenu","enquiry");
		model.addObject("menuName","enquiry");
		return model;
	}
	
	@RequestMapping(value = "/get_helpxls.do", method = RequestMethod.GET)
	public String pinting(HttpSession session, HttpServletRequest request,
			HttpServletResponse response) throws Exception {
		Business bus = (Business) session.getAttribute("business");

		String idno = request.getParameter("attno");
		int id=Integer.parseInt(idno);
		
		AttachmentHistory att = attachmentDAO.getAttachmentHistoryByPI(id);
		logger.info("the business id is  " + bus.getBusinessId());
		byte[] pdfReport = att.getFileBlob();
		logger.info("the length of pdf is" + pdfReport.length);
		logger.info("the length of pdf is" + pdfReport.toString());
		ServletOutputStream servletOutputStream = response.getOutputStream();
		String fileName = "sample.xls";
		response.setHeader("Content-Disposition", "inline; filename=" + fileName);
		response.setContentLength(pdfReport.length);
		servletOutputStream.write(pdfReport);
		servletOutputStream.flush();
		servletOutputStream.close();
		return null;

	}
	//this is to get the groups of an client organization
	@RequestMapping("/getGroups.do")
	public @ResponseBody
	String getGroups(HttpServletRequest req,HttpSession session,ModelAndView model){
		Business bus=(Business)session.getAttribute("business");
		int busId=bus.getBusinessId();
		String org=req.getParameter("orgn");
		logger.info("organisation "+org);
		
		String groupList="";
		try {
			groupList=clientdetDAO.getGroups(busId,org);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return groupList;
		
	}
	//this is to get the divisions of an client organization
	@RequestMapping("/getDiv.do")
	public @ResponseBody
	String getDiv(HttpServletRequest req,HttpSession session,ModelAndView model){
		Business bus=(Business)session.getAttribute("business");
		int busId=bus.getBusinessId();
		String org=req.getParameter("orgn");
		logger.info("organisation "+org);
		String divList="";
		try {
			divList=clientdetDAO.getDiv(busId,org);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return divList;
		
	}
	//getting the users of the division of an organization
	@RequestMapping("/getUsers.do")
	public @ResponseBody
	String getUsers(HttpServletRequest req,HttpSession session,ModelAndView model){
		Business bus=(Business)session.getAttribute("business");
		int busId=bus.getBusinessId();
		String div=req.getParameter("division");
		String org=req.getParameter("org");
		logger.info("division "+div);
		List<Object> userList=null;
		try {
			userList=clientdetDAO.getUser(busId,org,div);
			System.out.println("the users list is"+userList);
		} catch (Exception e) {
			e.printStackTrace();
		}
		StringBuilder sb=new StringBuilder();
		if(userList!=null){
			for(int i=0;i<userList.size();i++){
				sb.append(userList.get(i)+",");
			}
			return sb.toString();
		}else {
			return "";
		}
		
	}
	//getting all the executives of a company
	@RequestMapping("/getExec.do")
	public @ResponseBody
	String getExec(HttpServletRequest req,HttpSession session,ModelAndView model){
		Business bus=(Business)session.getAttribute("business");
		int busId=bus.getBusinessId();
		String shrtName=req.getParameter("company");
		if(!shrtName.equals("Please Select")){
			logger.info("company ShartName "+shrtName);
			
			executivesList=new ArrayList<Executives>();
			try {
				executivesList=executivesDAO.getExec(busId);
			}catch(NullPointerException npe){
				npe.printStackTrace();
				logger.info("Executives Are Not Available For This Business");
			}catch (Exception e) {
				e.printStackTrace();
			}
			StringBuilder sb=new StringBuilder();
				for(int i=0;i<executivesList.size();i++){;
					sb.append(executivesList.get(i).getExeName()+",");
				}
			return sb.toString();
		}
		return null;
	}
	//generating enquiry number
	@RequestMapping("/getEnqNo.do")
	public @ResponseBody
	String getEnqNo(HttpServletRequest req,HttpSession session,ModelAndView model){
		Business bus=(Business)session.getAttribute("business");
		
		String org=req.getParameter("org");
		String div=req.getParameter("div");
		String eqtype=req.getParameter("eType");
		logger.info("enq type is "+eqtype);
		Clientdet cli=null;
		String enq = "";
		
		if (!org.equals("Please Select") && !div.equals("Please Select") && !eqtype.equals("Please Select") && !eqtype.trim().equals("TE")) {
			try {
				cli = clientdetDAO.getShrtName(bus.getBusinessId(),org);
			} catch (Exception e) {
				e.printStackTrace();
			}
			logger.info("client ShartName "+cli.getShrtname());
			Long id=null;
			
			String shrtname=cli.getShrtname();
			StringBuilder str = null;
			
			enq = "";
			
			if (!eqtype.trim().equals("TE")) {
				str = new StringBuilder();
				logger.info("Enquiry Type Is:"+eqtype);
				
				try {
					id=OpportunityDAO.getSingleInstance().maxid(bus.getBusinessId(),eqtype);
				} catch (Exception e) {
					e.printStackTrace();
				}
				
				Calendar currentDate = Calendar.getInstance();
				  SimpleDateFormat formatter= 
				  new SimpleDateFormat("dd-MM-yyyy");
				  String dt = formatter.format(currentDate.getTime());
				  str.append(shrtname+"/"+div+"/"+eqtype+"/"+id+"/"+dt);
				enq = str.toString();
			}
			
		}	
		return enq;
	}
	//on submitting the new enquiry form,the details will be saved
	@RequestMapping(value="/save.do",method = RequestMethod.POST)
	public ModelAndView insert(HttpServletRequest req,HttpSession session, ModelAndView model) throws Exception{
		Business bus=(Business)session.getAttribute("business");
		if(bus==null)return new ModelAndView("/jsp/index.jsp");
		partsList=new ArrayList<Parts>();
		int busid=bus.getBusinessId();
		User us=(User)session.getAttribute("user");
		String org=req.getParameter("org");
		String div=req.getParameter("group");
		String user=req.getParameter("user");
		String eqtype=req.getParameter("eType");
		logger.info("enq type "+eqtype);
		String eqNo=req.getParameter("enqNum");
		String techBid=req.getParameter("techBid");
		logger.info("bid type "+techBid);
		Boolean tchBid=false;
		if(techBid!=null && techBid.length()!=0 && techBid!=""){
			tchBid=true;
		}
		String priceBid=req.getParameter("priceBid");
		Boolean prcBid=false;
		if(priceBid!=null && priceBid.length()!=0 && priceBid!=""){
		prcBid=true;
		}
		String warrant=req.getParameter("warranty");
		Boolean warranty=false;
		if(warrant!=null && warrant.length()!=0 && warrant!=""){
			warranty=true;
		}
		String dDate=req.getParameter("dDate");
		logger.info("due date "+dDate);
		DateFormat formatter = new SimpleDateFormat("dd-MM-yyyy");
		Date  date = formatter.parse(dDate); 
		
		
		String prob=req.getParameter("prob");
		int probability=Integer.parseInt(prob);
		String projName=req.getParameter("projName");
		String comp=req.getParameter("comp");
		
		String executive=req.getParameter("exeName");
		String quotCurr=req.getParameter("quotCurr");
		String opprName=req.getParameter("opprName");
		Calendar currentDate = Calendar.getInstance();
		DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
		String dateNow = formater.format(currentDate.getTime());
		Date cdate=formater.parse(dateNow);
		//logger.info("current date "+cdate.getTime());
		
		String crdate=formatter.format(cdate.getTime());
		String rcount=req.getParameter("rowcount");
		int rowcount=Integer.parseInt(rcount);
		logger.info("row count "+rowcount);
		String status= Constants.ST_PENDING;
		
		
		Clientdet client=clientdetDAO.getCliOrg(bus.getBusinessId(),org);
		
		String refno="";
		
		logger.info("New Opportunity Created With Reference Number "+refno);
		StringBuilder ref=new StringBuilder();
		Long id=OpportunityDAO.getSingleInstance().maxid(bus.getBusinessId(),eqtype);
		ref=ref.append(comp+"/"+eqtype+"/"+id+"/"+crdate);
		refno=ref.toString();
		
		try {
			opportunity=new Opportunity(bus,org,div,user,eqtype,eqNo,refno,tchBid,prcBid,warranty,new java.sql.Date(date.getTime()),new java.sql.Date(cdate.getTime()),probability,projName,client,executive,quotCurr,opprName,status);
		} catch (NullPointerException e) {
			e.printStackTrace();
		}
		StringBuilder partsb=new StringBuilder();
		partsb.append("<table border=1 width='890'><tr><th width='30'>S.No</th> <th width='200'> Part</th><th width='50'> Quantity</th> <th width='200'> Make</th> <th width='200'> Description </th><th width='200'> Application </th><th width='200'> Specification </th></tr>" );
		//get parts details
		Parts pr=null;
		for(int i=1;i<=rowcount;i++){
			try{
			String partNum=req.getParameter("partno"+i).toUpperCase();
			logger.info("part number "+partNum);
			String qt=req.getParameter("qty"+i);
			logger.info("qty "+qt);
			int qty=0;
			try{
				if(qty+""!=null){
					qty=Integer.parseInt(qt.trim());
				}
			}catch(NumberFormatException nfe){
				nfe.printStackTrace();
			}catch(NullPointerException npe){
				logger.info("Nullpointer exception occured because quantity is null");
				npe.printStackTrace();
			}
			String make=req.getParameter("make"+i);
			String desc=req.getParameter("desc"+i);
			String appli=req.getParameter("application"+i);
			String specf=req.getParameter("specification"+i);
			
			
			partsb.append("<tr>");
			partsb.append("<td>");
			partsb.append(i);
			partsb.append("</td>");
			
			partsb.append("<td>");
			partsb.append(partNum);
			partsb.append("</td>");
			
			partsb.append("<td>");
			partsb.append(qty);
			partsb.append("</td>");
			
			partsb.append("<td>");
			partsb.append(make);
			partsb.append("</td>");
			
			partsb.append("<td>");
			partsb.append(desc);
			partsb.append("</td>");
			
			partsb.append("<td>");
			partsb.append(appli);
			partsb.append("</td>");
			
			partsb.append("<td>");
			partsb.append(specf);
			partsb.append("</td>");
			partsb.append("</tr>");
			
			if(partNum==null&&desc==null)
				continue;
			
		
			try {
				if(qty!=0){
					pr=new Parts(bus,partNum,qty,make,desc,appli,specf);
				}
			} catch (NullPointerException e) {
				e.printStackTrace();
			}
			partsList.add(pr);
			}catch(NullPointerException npe){
				npe.printStackTrace();
			}
		}
		opportunity.setPartsset(partsList);
		partsb.append("</table>");
		
		try{
			//inserting the opportunity details into opportunity table
			OpportunityDAO.getSingleInstance().insert(opportunity);
			model.addObject("msgsrc","Successfully Created Enquiry and Reference Number is   ' "+refno+" '");
			model.setViewName("redirect:/exportsource/open.do");
			if(req.getParameter("refNo")==null){
				SendMail sm=new SendMail();
				String from=us.getEmail();
				String pass=us.getEmailPwd();
				String subject="Reference Number : "+refno;
				StringBuilder body=new StringBuilder();
				body.append("Dear Sir/Madam, <br/>\n")
				.append(Constants.ST_PE_BODY+"<br/><br/> \n ")
				.append("Below Are The Parts Details.<br/> \n")
				.append(partsb+"<br/>")
				.append("<br/>")
				.append(" Thanks & Regards <br/>\n \n")
				.append(" For "+bus.getBusinessName()+" <br/>\n")
				.append("Manager - Operations <br/>\n \n ")
				.append(bus.getAddress()+" <br/> \n ")
				.append("Tel :"+bus.getPhone()+";"+"Fax:"+bus.getFax()+" <br/> \n")
				.append(" Mobile office: +91 94411 20210 <br/>");
			
				ClientUser clUser=clientdetDAO.findUserDet(user,client.getId());
				
				//sending a mail to the user who has given of this enquiry,to executive
				try{
					String toEmail=clUser.getEmail();
					ExecutivesDAO exdao=new ExecutivesDAO();
					Executives ex=exdao.findByName(executive, busid);
					String exEmail=ex.getEmailId();
					sm.sendMailNoFile(from, pass, body.toString(), subject, toEmail,exEmail);
				}catch (Exception e) {
					e.printStackTrace();
					System.out.println("mail is not sent to the client");
				}
			}
		}catch(ConstraintViolationException cve){
			//redirecting user to enquiry page with error message when enquiry is not saved.
			cve.printStackTrace();
			model.addObject("msg","Failed To Create Enquiry Caused By Constraint Violation Exception.");
			model.setViewName("redirect:open.do");
		}
		catch(Exception e){
			//redirecting user to enquiry page with error messagewhen enquiry is not saved.
			e.printStackTrace();
			model.addObject("msg","Failed To Create Enquiry.");
			model.setViewName("redirect:open.do");
		}
		partsList=null;
		model.addObject("currentref",refno);
		model.addObject("selectedRef",refno);
		return model;
		
	}
	//updating the enquiry information
	@RequestMapping(value="/editsave.do",method = RequestMethod.POST)
	public ModelAndView editinsert(HttpServletRequest req,HttpSession session, ModelAndView model) throws Exception{
		//OpportunityDAO.getSingleInstance() opDao =new OpportunityDAO.getSingleInstance()();
		partsList=new ArrayList<Parts>();
		Business bus=(Business)session.getAttribute("business");
		if(bus==null)return new ModelAndView("/jsp/index.jsp");

		
		String org=req.getParameter("org");
		String div=req.getParameter("div");
		String user=req.getParameter("user");
		String eqtype=req.getParameter("eType");
		System.out.println("enq type "+eqtype);
		String eqNo=req.getParameter("enqNum");
		String techBid=req.getParameter("techBid");
		System.out.println("bid type "+techBid);
		Boolean tchBid=false;
		if(techBid!=null && techBid.length()!=0 && techBid!=""){
			tchBid=true;
		}
		String priceBid=req.getParameter("priceBid");
		Boolean prcBid=false;
		if(priceBid!=null && priceBid.length()!=0 && priceBid!=""){
		prcBid=true;
		}
		String dDate=req.getParameter("dDate");
		System.out.println("due date "+dDate);
		DateFormat formatter = new SimpleDateFormat("dd-MM-yyyy");
		Date  date = formatter.parse(dDate); 
		String prob=req.getParameter("prob");
		int probability=Integer.parseInt(prob);
		String projName=req.getParameter("projName");
		
		String executive=req.getParameter("exeName");
		String quotCurr=req.getParameter("quotCurr");
		String opprName=req.getParameter("opprName");
		
		String warrant=req.getParameter("warranty");
		Boolean warranty=false;
		if(warrant!=null && warrant.length()!=0 && warrant!=""){
			warranty=true;
		}
		
		Calendar currentDate = Calendar.getInstance();
		DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
		String dateNow = formater.format(currentDate.getTime());
		Date cdate=formater.parse(dateNow);
		//System.out.println("current date "+cdate.getTime());
		String rcount=req.getParameter("rowcount");
		int rowcount=Integer.parseInt(rcount);
		System.out.println("row count "+rowcount);
		String status= Constants.ST_PENDING;
		
		//ClientdetDAO clDAO=new ClientdetDAO();
		Clientdet client=clientdetDAO.getCliOrg(bus.getBusinessId(),org);
		Opportunity oppr=null;
		String refno="";
		int opid=0;
		if(req.getParameter("refNo")!=null){
			refno=req.getParameter("refNo");
			opid=Integer.parseInt(req.getParameter("id"));
			try {
				oppr=new Opportunity(opid,bus,org,div,user,eqtype,eqNo,refno,tchBid,prcBid,warranty,new java.sql.Date(date.getTime()),new java.sql.Date(cdate.getTime()),probability,projName,client,executive,quotCurr,opprName,status);
			} catch (NullPointerException e) {
				e.printStackTrace();
			}
		}
		int qty=0;
		
		for(int i=1;i<=rowcount;i++){
		System.out.println("the rowcount is"+rowcount);
			String partid=req.getParameter("partid"+i);
			System.out.println("the partid is"+partid);
			String partNum=req.getParameter("partno"+i);
			if(partNum!=null)
			{partNum=partNum.toUpperCase();
			System.out.println("part number "+partNum);
			}
			String qt=req.getParameter("qty"+i);
			System.out.println("qty "+qt);
			if(qt!=null)
			{
			 qty=Integer.parseInt(qt);
			}
			
			String make=req.getParameter("make"+i);
			String desc=req.getParameter("desc"+i);
		
			String appli=req.getParameter("application"+i);
			String specf=req.getParameter("specification"+i);
			if(partNum==null&&desc==null)
				continue;
			Parts pr=null;
		if(partid==null)
		{try {
			pr=new Parts(oppr.getId(),bus,partNum,qty,make,desc,appli,specf);
		}catch (NullPointerException e) {
			e.printStackTrace();
		}
		}
		else
		{int id=Integer.parseInt(partid);
			try {
				pr=new Parts(id,oppr.getId(),bus,partNum,qty,make,desc,appli,specf);
			} catch (NullPointerException e) {
				e.printStackTrace();
			}	
		}
		partsList.add(pr);
		
		}
		
		System.out.println("the partset is "+partsList);
		oppr.setPartsset(partsList);
		try{
			OpportunityDAO.getSingleInstance().insert(oppr);
			model.addObject("msgsrc","Successfully Created Enquiry and Reference Number is   ' "+refno+" '");
			model.setViewName("redirect:/exportsource/open.do");
		}catch(ConstraintViolationException cve){
			cve.printStackTrace();
			model.addObject("msg","Constraint Violation Exception Occured While Creating Enquiry.");
			model.setViewName("redirect:open.do");
		}
		catch(Exception e){
			e.printStackTrace();
			model.addObject("msg","Failed To Create Enquiry.");
			model.setViewName("redirect:open.do");
		}
			
		
		model.addObject("currentref",refno);
		model.addObject("selectedRef",refno);
		return model;
		
	}
	//getting the data of excel sheet and displaying
	@RequestMapping("/uploadExcel.do")
	public ModelAndView uploadExcel(HttpServletRequest request, HttpSession session,ModelAndView model) throws Exception{
		Business bus=(Business)session.getAttribute("business");
		int busId=bus.getBusinessId();
		clientsList=new ArrayList<Clientdet>();
		System.out.println("Starting Of Excel file upload");
		ReadExcel test = new ReadExcel();
		MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
		MultipartFile file = null;
		try{
			file=multipartRequest.getFile("excel");
			System.out.println("In Try Ending");
		}catch(NullPointerException npe){
			npe.printStackTrace();
		}
		byte[] b=file.getBytes();
		System.out.println("File Bytes Is: "+b);
		String s=null;
		try{
			File f=new File("sample.xls");
			f.createNewFile();
			FileOutputStream fos=null;
			if(f != null){
				fos=new FileOutputStream(f);
				fos.write(b);
				fos.flush();
				fos.close();
			}
			System.out.println("Location Of File Is: "+fos);
			test.setInputFile("sample.xls");
			s=test.read();
			System.out.println("Ending Of Excel file upload");
			//System.out.println("XL File After Reading"+s);
		}catch(Exception e){
			e.printStackTrace();
			model.setViewName("/jsp/New Enquiry.jsp");
			model.addObject("submenu","enquiry");
			model.addObject("menuName","enquiry");
			model.addObject("msg","An Error Occured While Uploading Excel File");
			return model;
		}
		try {
			clientsList=clientdetDAO.getorg(busId);
		} catch (Exception e) {
			e.printStackTrace();
		}
		executivesList=executivesDAO.findAll(busId);
		model.setViewName("/jsp/New Enquiry.jsp");
		model.addObject("orgList",clientsList);
		model.addObject("executives",executivesList);
		model.addObject("xlfile",s);
		model.addObject("submenu","enquiry");
		model.addObject("menuName","enquiry");
		return model;
	}
	@RequestMapping("/showStock.do")
	public ModelAndView ShowStock(HttpSession session,HttpServletRequest request,ModelAndView model){
		Business business=(Business)session.getAttribute("business");
		String parts=request.getParameter("parts");
		//System.out.println("Parts Are:"+parts);
		if(parts!=null){
			String temp[]=parts.split(Constants.ST_SEPARATOR);
			StringBuilder sb=new StringBuilder();
			for(int i=0;i<temp.length;i++){
				sb.append("'"+temp[i]+"'");
				if(i!=temp.length-1)
					sb.append(",");
			}
			List<StockEntity> lse=null;
			try {
				lse=stockDAO.findStock(sb.toString(),business.getBusinessId());
			} catch (Exception e) {
				e.printStackTrace();
			}
			model=new ModelAndView("/jsp/ShowStock.jsp")
			.addObject("stock",lse);
			return model;
		}
		return model;
	}
}
